package com.xch.dao.impl;

import com.xch.bean.Courier;
import com.xch.bean.User;
import com.xch.dao.BaseUserDao;
import com.xch.exception.DuplicateCodeException;
import com.xch.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDaoMysql implements BaseUserDao {
    private static final String SQL_FIND_ALL = "SELECT * FROM USER";
    private static final String SQL_FIND_LIMIT = "SELECT * FROM USER LIMIT ?,?";
    private static final String SQL_TOTAL = "SELECT COUNT(1) FROM USER";
    private static final String SQL_FIND_BY_PHONE = "SELECT * FROM USER WHERE USERPHONE=?";
    private static final String SQL_INSERT = "INSERT INTO USER (NICKNAME,USERPHONE,PASSWORD,USERREGISTERTIME,USERLOGINTIME) values(?,?,?,Now(),Now())";
    private static final String SQL_UPDATE = "UPDATE USER SET NICKNAME=?,USERPHONE=?,PASSWORD=? WHERE ID=?";
    private static final String SQL_DELETE = "DELETE FROM USER WHERE ID=?";

    @Override
    public List<User> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<User> data = new ArrayList<>();
        //1.获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.预编译sql语句
        try {
            if(limit){
                state = conn.prepareStatement(SQL_FIND_LIMIT);
                state.setInt(1,offset);
                state.setInt(2,pageNumber);
            }else{
                state = conn.prepareStatement(SQL_FIND_ALL);
            }
            result = state.executeQuery();
            //3.填充参数
            while(result.next()){
                int id = result.getInt("id");
                String nickName = result.getString("nickName");
                String userPhone = result.getString("userPhone");
                String password = result.getString("password");
                Timestamp userRegisterTime = result.getTimestamp("userRegisterTime");
                Timestamp userLoginTime = result.getTimestamp("userLoginTime");
                User c = new User(id,nickName,userPhone,password,userRegisterTime,userLoginTime);
                data.add(c);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,result);
        }
        return data;
    }

    @Override
    public int total() {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        int num = 0;
        try {
            state = conn.prepareStatement(SQL_TOTAL);
            result =  state.executeQuery();
            System.out.println(result.next());
            num = result.getInt(1);
            return num;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,result);
        }
        return num;
    }

    @Override
    public User findByPhone(String userPhone) {
        //1.获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.预编译sql语句
        try {
            state = conn.prepareStatement(SQL_FIND_BY_PHONE);
            //3.填充参数
            state.setString(1,userPhone);
            //4.执行sql语句
            result = state.executeQuery();
            //5.获取执行的结果
            if (result.next()){
                int id = result.getInt("id");
                String nickName = result.getString("nickName");
                String password = result.getString("password");
                Timestamp userRegisterTime = result.getTimestamp("userRegisterTime");
                Timestamp userLoginTime = result.getTimestamp("userLoginTime");
                User u = new User(id,nickName,userPhone,password,userRegisterTime,userLoginTime);
                return u;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,result);
        }

        return null;
    }

    @Override
    public boolean insert(User u) {
        //1.获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.预编译sql语句
        try {
            state = conn.prepareStatement(SQL_INSERT);
            //3.填充参数
            state.setString(1,u.getNickName());
            state.setString(2,u.getUserPhone());
            state.setString(3,u.getPassword());
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    @Override
    public boolean update(int id, User newUser) {
        //1.获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.预编译语句
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            //3.设置参数
            state.setString(1,newUser.getNickName());
            state.setString(2,newUser.getUserPhone());
            state.setString(3,newUser.getPassword());
            state.setInt(4,id);
            //4,5 执行sql语句并获取结果
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    @Override
    public boolean delete(int id) {
        //1.获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.预编译sql语句
        try {
            state=conn.prepareStatement(SQL_DELETE);
            state.setInt(1,id);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }
}
